﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using DBUtility;
using Model;
using MySql.Data.MySqlClient;

namespace DAL
{
    public class DDriver
    {
        public DriverInfo CheckCard(string cardId, string password)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from tdriver");
            strSql.Append(" where CardId=@CardId and Password=@Password");
            MySqlParameter[] parameters = {
					new MySqlParameter("@CardId", MySqlDbType.VarChar,20),
                    new MySqlParameter("@Password", MySqlDbType.VarChar,20)
            };
            parameters[0].Value = cardId;
            parameters[1].Value = password;
            bool result = DbHelperMySQL.Exists(strSql.ToString(), parameters);
            if (!result)
            {
                return null;
            }
            DriverInfo driver = GetModelByCardId(cardId);
            return driver;
        }
         
        public bool ExistsCardId(string cardId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from tdriver");
            strSql.Append(" where CardId=@CardId");
            MySqlParameter[] parameters = {
					new MySqlParameter("@CardId", MySqlDbType.VarChar,20)
            };
            parameters[0].Value = cardId;

            return DbHelperMySQL.Exists(strSql.ToString(), parameters);
        }
   
        public bool Add(DriverInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into tdriver(");
            strSql.Append("Name,Gender,CardId,Password,");
            strSql.Append("State,Remark,CarId,PhoneNumber)");
            strSql.Append(" values (");
            strSql.Append("@Name,@Gender,@CardId,@Password,");
            strSql.Append("@State,@Remark,@CarId,@PhoneNumber)");
            MySqlParameter[] parameters = {
					new MySqlParameter("@Name", MySqlDbType.VarChar,10),
					new MySqlParameter("@Gender", MySqlDbType.VarChar,2),
					new MySqlParameter("@CardId", MySqlDbType.VarChar,20),
					new MySqlParameter("@Password", MySqlDbType.VarChar,20),
                    new MySqlParameter("@State", MySqlDbType.Int32),
					new MySqlParameter("@Remark", MySqlDbType.Text),
                    new MySqlParameter("@CarId", MySqlDbType.Int32),
                    new MySqlParameter("@PhoneNumber", MySqlDbType.VarChar,15)};
            parameters[0].Value = model.Name;
            parameters[1].Value = model.Gender;
            parameters[2].Value = model.CardId;
            parameters[3].Value = model.Password;
            parameters[4].Value = model.State;
            parameters[5].Value = model.Remark;
            parameters[6].Value = model.TaxiId;
            parameters[7].Value = model.PhoneNumber;

            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
       
        public bool Update(DriverInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update tdriver set ");
            strSql.Append("Name=@Name,");
            strSql.Append("Gender=@Gender,");
            strSql.Append("CardId=@CardId,");
            strSql.Append("Password=@Password,");
            strSql.Append("State=@State,");
            strSql.Append("Remark=@Remark,");
            strSql.Append("CarId=@CarId,");
            strSql.Append("PhoneNumber=@PhoneNumber");
            strSql.Append(" where id=@id");
            MySqlParameter[] parameters = {
					new MySqlParameter("@Name", MySqlDbType.VarChar,10),
					new MySqlParameter("@Gender", MySqlDbType.VarChar,2),
					new MySqlParameter("@CardId", MySqlDbType.VarChar,20),
                    new MySqlParameter("@Password", MySqlDbType.VarChar,20),
					new MySqlParameter("@State", MySqlDbType.VarChar,10),
					new MySqlParameter("@Remark", MySqlDbType.Text),
                    new MySqlParameter("@CarId", MySqlDbType.Int32,10),
                    new MySqlParameter("@PhoneNumber", MySqlDbType.VarChar,15),
					new MySqlParameter("@id", MySqlDbType.Int32,10)};
            parameters[0].Value = model.Name;
            parameters[1].Value = model.Gender;
            parameters[2].Value = model.CardId;
            parameters[3].Value = model.Password;
            parameters[4].Value = model.State;
            parameters[5].Value = model.Remark;
            parameters[6].Value = model.TaxiId;
            parameters[7].Value = model.PhoneNumber;
            parameters[8].Value = model.Id;

            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public bool UpdateState(string phoneNumber,int state)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update tdriver set ");             
            strSql.Append("State=@State");            
            strSql.Append(" where PhoneNumber=@PhoneNumber");
            MySqlParameter[] parameters = {					 
					new MySqlParameter("@State", MySqlDbType.VarChar,10),					
					new MySqlParameter("@PhoneNumber", MySqlDbType.VarChar,15)};
            parameters[0].Value = state;
            parameters[1].Value = phoneNumber;
             
            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public bool ChangePassword(string cardId,string oldPwd,string newPwd)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update tdriver set ");
            strSql.Append("Password=@Password");
            strSql.Append(" where CardId=@CardId and Password=@Password2");
            MySqlParameter[] parameters = {					 
					new MySqlParameter("@Password", MySqlDbType.VarChar,20),					
					new MySqlParameter("@CardId", MySqlDbType.VarChar,20),
                    new MySqlParameter("@Password2", MySqlDbType.VarChar,20)};
            parameters[0].Value = newPwd;
            parameters[1].Value = cardId;
            parameters[2].Value = oldPwd;
            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public bool Delete(int id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from tdriver ");
            strSql. Append(" where id=@id");
            MySqlParameter[] parameters = {
					new MySqlParameter("@id", MySqlDbType.Int32)
};
            parameters[0].Value = id;

            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        
        public DriverInfo GetModelByPhoneNum(string phoneNumber)
        {

            StringBuilder strSql = new StringBuilder(); 
            strSql.Append("select  t.Id,t.Name,t.Gender,t.CardId,t.Password,");
            strSql.Append("t.State,t.CarId,t.CompanyId,t.Remark,");
            strSql.Append("tb.TaxiNo,tb.PhoneNumber as CarPhoneNumber,tc.CompanyName ");
            strSql.Append("from tdriver t left join ttaxi tb on ");
            strSql.Append("t.TaxiId=tb.Id left join tcompany tc on ");
            strSql.Append("t.CompanyId = tc.Id");            
            strSql.Append(" where t.PhoneNumber =@PhoneNumber");
            MySqlParameter[] parameters = {
					new MySqlParameter("@PhoneNumber", MySqlDbType.VarChar,15)};
            parameters[0].Value = phoneNumber;

            DriverInfo model = new Model.DriverInfo();
            DataSet ds = DbHelperMySQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["Id"] != null && ds.Tables[0].Rows[0]["Id"].ToString() != "")
                {
                    model.Id = int.Parse(ds.Tables[0].Rows[0]["Id"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Name"] != null && ds.Tables[0].Rows[0]["Name"].ToString() != "")
                {
                    model.Name = ds.Tables[0].Rows[0]["Name"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Gender"] != null && ds.Tables[0].Rows[0]["Gender"].ToString() != "")
                {
                    model.Gender = ds.Tables[0].Rows[0]["Gender"].ToString();
                }
                if (ds.Tables[0].Rows[0]["CardId"] != null && ds.Tables[0].Rows[0]["CardId"].ToString() != "")
                {
                    model.CardId = ds.Tables[0].Rows[0]["CardId"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Password"] != null && ds.Tables[0].Rows[0]["Password"].ToString() != "")
                {
                    model.Password = ds.Tables[0].Rows[0]["Password"].ToString();
                }
                if (ds.Tables[0].Rows[0]["State"] != null && ds.Tables[0].Rows[0]["State"].ToString() != "")
                {
                    model.State = int.Parse(ds.Tables[0].Rows[0]["State"].ToString());
                }
                if (ds.Tables[0].Rows[0]["CarId"] != null && ds.Tables[0].Rows[0]["CarId"].ToString() != "")
                {
                    model.TaxiId = int.Parse(ds.Tables[0].Rows[0]["CarId"].ToString());
                }
                if (ds.Tables[0].Rows[0]["CompanyId"] != null && ds.Tables[0].Rows[0]["CompanyId"].ToString() != "")
                {
                    model.CompanyId = int.Parse(ds.Tables[0].Rows[0]["CompanyId"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Remark"] != null && ds.Tables[0].Rows[0]["Remark"].ToString() != "")
                {
                    model.Remark = ds.Tables[0].Rows[0]["Remark"].ToString();
                }
                if (ds.Tables[0].Rows[0]["CarPhoneNumber"] != null && ds.Tables[0].Rows[0]["CarPhoneNumber"].ToString() != "")
                {
                    model.CarPhoneNumber = ds.Tables[0].Rows[0]["CarPhoneNumber"].ToString();
                }
                if (ds.Tables[0].Rows[0]["CompanyName"] != null && ds.Tables[0].Rows[0]["CompanyName"].ToString() != "")
                {
                    model.CompanyName = ds.Tables[0].Rows[0]["CompanyName"].ToString();
                }
                return model;
            }
            else
            {
                return null;
            }
        }

        public DriverInfo GetModelByCardId(string cardId)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  t.Id,t.Name,t.Gender,t.CardId,t.Password,");
            strSql.Append("t.State,t.CarId,t.CompanyId,t.Remark,t.PhoneNumber,");
            strSql.Append("tb.TaxiNo,tb.PhoneNumber as CarPhoneNumber,tc.CompanyName ");
            strSql.Append("from tdriver t left join ttaxi tb on ");
            strSql.Append("t.CarId=tb.Id left join tcompany tc on ");
            strSql.Append("t.CompanyId = tc.Id");
            strSql.Append(" where t.CardId =@CardId");
            MySqlParameter[] parameters = {
					new MySqlParameter("@CardId", MySqlDbType.VarChar,20)};
            parameters[0].Value = cardId;

            DriverInfo model = new DriverInfo();
            DataSet ds = DbHelperMySQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["Id"] != null && ds.Tables[0].Rows[0]["Id"].ToString() != "")
                {
                    model.Id = int.Parse(ds.Tables[0].Rows[0]["Id"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Name"] != null && ds.Tables[0].Rows[0]["Name"].ToString() != "")
                {
                    model.Name = ds.Tables[0].Rows[0]["Name"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Gender"] != null && ds.Tables[0].Rows[0]["Gender"].ToString() != "")
                {
                    model.Gender = ds.Tables[0].Rows[0]["Gender"].ToString();
                }
                if (ds.Tables[0].Rows[0]["CardId"] != null && ds.Tables[0].Rows[0]["CardId"].ToString() != "")
                {
                    model.CardId = ds.Tables[0].Rows[0]["CardId"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Password"] != null && ds.Tables[0].Rows[0]["Password"].ToString() != "")
                {
                    model.Password = ds.Tables[0].Rows[0]["Password"].ToString();
                }
                if (ds.Tables[0].Rows[0]["PhoneNumber"] != null && ds.Tables[0].Rows[0]["PhoneNumber"].ToString() != "")
                {
                    model.PhoneNumber = ds.Tables[0].Rows[0]["PhoneNumber"].ToString();
                }
                if (ds.Tables[0].Rows[0]["State"] != null && ds.Tables[0].Rows[0]["State"].ToString() != "")
                {
                    model.State = int.Parse(ds.Tables[0].Rows[0]["State"].ToString());
                }
                if (ds.Tables[0].Rows[0]["CarId"] != null && ds.Tables[0].Rows[0]["CarId"].ToString() != "")
                {
                    model.TaxiId = int.Parse(ds.Tables[0].Rows[0]["CarId"].ToString());
                }
                if (ds.Tables[0].Rows[0]["CompanyId"] != null && ds.Tables[0].Rows[0]["CompanyId"].ToString() != "")
                {
                    model.CompanyId = int.Parse(ds.Tables[0].Rows[0]["CompanyId"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Remark"] != null && ds.Tables[0].Rows[0]["Remark"].ToString() != "")
                {
                    model.Remark = ds.Tables[0].Rows[0]["Remark"].ToString();
                }
                if (ds.Tables[0].Rows[0]["CarPhoneNumber"] != null && ds.Tables[0].Rows[0]["CarPhoneNumber"].ToString() != "")
                {
                    model.CarPhoneNumber = ds.Tables[0].Rows[0]["CarPhoneNumber"].ToString();
                }
                if (ds.Tables[0].Rows[0]["CompanyName"] != null && ds.Tables[0].Rows[0]["CompanyName"].ToString() != "")
                {
                    model.CompanyName = ds.Tables[0].Rows[0]["CompanyName"].ToString();
                }
                return model;
            }
            else
            {
                return null;
            }
        }

        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  t.Id,t.Name,t.Gender,t.CardId,t.Password,");
            strSql.Append("t.State,t.CarId,t.CompanyId,t.Remark,t.PhoneNumber,");
            strSql.Append("tb.TaxiNo,tb.PhoneNumber as CarPhoneNumber,tc.CompanyName ");
            strSql.Append("from tdriver t left join ttaxi tb on ");
            strSql.Append("t.CarId=tb.Id left join tcompany tc on ");
            strSql.Append("t.CompanyId = tc.Id");            
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperMySQL.Query(strSql.ToString());
        }
 
    }
}
